Public Class frmBaoCaoKiemKeTheoNhomHang
    Dim ma_hang As String = ""
    Dim m_loaikho As String = ""

    Private Sub frmBaoCaoKiemKeTheoNhomHang_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        KhoiTaocbbKyKiemKe()
    End Sub

    Public Sub KhoiTaocbbKyKiemKe()
        cbbKyKiemKe.Items.Clear()
        cbbKyKiemKe.Items.Add("Tháng 1")
        Dim strsql As String
        strsql = "select count(*) from tblDPKK where Year(ngayKK) = '" & CStr(Now.Year) & "'"
        Dim value As Integer
        value = BaseDB.ExecSql_DataValue(strsql)
        If (value <> 0) Then
            cbbKyKiemKe.Items.Add("Tháng 7")
        End If
    End Sub

    Private Sub txtLoaiThuoc_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtLoaiThuoc.DoubleClick
        ShowFrmChonLoaiThuoc()
    End Sub

    Private Sub txtLoaiThuoc_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtLoaiThuoc.KeyDown
        If (e.KeyCode = Keys.Enter) Or (e.KeyCode = Keys.F1) Then
            ShowFrmChonLoaiThuoc()
        End If
        If (e.KeyCode = Keys.Delete) And (e.Modifiers = Keys.Control) Then
            txtLoaiThuoc.Text = ""
            ma_hang = ""
        End If
    End Sub

    Public Sub ShowFrmChonLoaiThuoc()
        Dim frm As New frmChonLoaiThuoc
        frm.ShowDialog()
        If (frm.Ten <> Nothing) Then
            Dim ten As String
            ten = frm.Ten
            txtLoaiThuoc.Text = ten
            If (frm.Loai) Then
                'm_maloaithuoc = CLoaiThuocs.GetLoaiThuocByTen(ten).MaLoaiThuoc
                ma_hang = CLoaiThuocs.GetLoaiThuocByTen(ten).MaLoaiThuoc
            Else
                Dim str() As String
                str = ten.Split("-")
                If (str.Length = 2) Then
                    ma_hang = CThuocs.GetThuocByTenandDVT(str(0).Trim, str(1).Trim).MaThuoc
                End If
            End If
        End If
    End Sub

    Private Sub btnBaoCao_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBaoCao.Click
        If (m_loaikho = "") Then
            ShowMessages("Bạn chưa chọn loại kho")
            Exit Sub
        End If
        Dim ds As DataSet
        ds = GetDs()
        Dim m_loaikiemke As Boolean
        If (cbbKyKiemKe.SelectedIndex = 0) Then
            m_loaikiemke = True
        Else
            m_loaikiemke = False
        End If
        Dim nnreport As New rptBaoCaoKiemKe
        nnreport.SetDataSource(ds.Tables(0))
        nnreport.SetParameterValue(0, m_loaikiemke)
        If (m_loaikho <> "") Then
            nnreport.SetParameterValue(1, CDoiTuongs.GetDoiTuongByMa(enumDOITUONG.LOAIKHO, m_loaikho).Ten)
        Else
            nnreport.SetParameterValue(1, "")
        End If
        Dim frm As New frmReportViewer(nnreport)
        frm.ShowDialog()
    End Sub

    Function GetDs() As DataSet
        Dim strsql = "select * from viewChiTietKiemKe "
        strsql &= " where lk = '" & m_loaikho & "'"

        If (ma_hang <> "") Then
            If (ma_hang.Substring(7, 7) = "0000000") Then 'loai thuoc
                Dim loaithuoc As String = ma_hang.Substring(0, 7)
                For i As Integer = 0 To 6
                    If (loaithuoc.Chars(6 - i) = "0") Then
                        loaithuoc = loaithuoc.Remove(6 - i, 1)
                    Else
                        Exit For
                    End If
                Next
                'lay left
                strsql &= " and left(ma," + CStr(loaithuoc.Length) + ") = '" + loaithuoc + "'"
            End If
        End If

        Dim m_loaikiemke As Boolean
        If (cbbKyKiemKe.SelectedIndex = 0) Then
            m_loaikiemke = True
            strsql &= " and slss11 <> 0 and slss11 is not NULL"
        Else
            m_loaikiemke = False
            strsql &= " and slss17 <> 0 and slss17 is not NULL"
        End If
        strsql &= " order by maloaithuoc"
        Dim ds As DataSet
        ds = BaseDB.ExecSql_DataSet(strsql)
        Return ds
    End Function

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        If (m_loaikho = "") Then
            ShowMessages("Bạn chưa chọn loại kho")
            Exit Sub
        End If
        Dim ds As DataSet
        ds = GetDs()
        ToEXCEL(Application.StartupPath & "\tempKiemKe.xls", ds)
    End Sub

    Private Sub ToEXCEL(ByVal mFileName2 As String, ByVal m_ds As DataSet)
        Dim mFileName As String = Application.StartupPath & "\KiemKe.xls"
        Dim m_loaikiemke As Boolean
        If (cbbKyKiemKe.SelectedIndex = 0) Then
            m_loaikiemke = True
        Else
            m_loaikiemke = False
        End If

        System.IO.File.Copy(mFileName, mFileName2, True)
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = CType(xlApp.Workbooks.Open(mFileName2), Excel.Workbook)

        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

        xlSheet.Activate()
        xlSheet.Application.Visible = True

        'dau phieu
        If (m_loaikiemke) Then
            xlSheet.Cells(4, 5) = "Số Lượng" + Chr(10) + "Kiểm Kê Ngày" + Chr(10) + "01/01/" + Now.Year.ToString
        Else
            xlSheet.Cells(4, 5) = "Số Lượng" + Chr(10) + "Kiểm Kê Ngày" + Chr(10) + "01/07/" + Now.Year.ToString
        End If

        Dim iRow0 As Integer = 7
        Dim len, i, j As Integer
        Dim tenloaithuoc As String
        Dim stt_kho As Integer = 0
        Dim gt_sosach, gt_kiemke As Double
        '
        i = 0
        j = 0
        '(i chay trong ds, j chay trong excel)
        len = m_ds.Tables(0).Rows.Count
        While (i < len)
            If (i >= len) Then
                Exit While
            End If
            tenloaithuoc = m_ds.Tables(0).Rows.Item(i).Item("ten_loaithuoc")
            xlSheet.Cells(iRow0 + j, 1) = "      " + tenloaithuoc
            mRange = xlSheet.Cells(iRow0 + j, 1)
            mRange.Font.Bold = True
            j += 1
            While (m_ds.Tables(0).Rows.Item(i).Item("ten_loaithuoc") = tenloaithuoc)
                xlSheet.Cells(iRow0 + j, 1) = m_ds.Tables(0).Rows.Item(i).Item("ten") + Chr(10) + "   " + ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("lo"))
                xlSheet.Cells(iRow0 + j, 2) = m_ds.Tables(0).Rows.Item(i).Item("dvt")
                xlSheet.Cells(iRow0 + j, 3) = m_ds.Tables(0).Rows.Item(i).Item("gle")
                xlSheet.Cells(iRow0 + j, 4) = ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("h"))
                If (m_loaikiemke) Then 'thang 1
                    xlSheet.Cells(iRow0 + j, 5) = m_ds.Tables(0).Rows.Item(i).Item("slkk11t")
                    '
                    xlSheet.Cells(iRow0 + j, 8) = m_ds.Tables(0).Rows.Item(i).Item("slss11")
                    xlSheet.Cells(iRow0 + j, 9) = m_ds.Tables(0).Rows.Item(i).Item("slkk11")
                    xlSheet.Cells(iRow0 + j, 10) = m_ds.Tables(0).Rows.Item(i).Item("slkk11") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                    If (m_ds.Tables(0).Rows.Item(i).Item("slkk11") > m_ds.Tables(0).Rows.Item(i).Item("slss11")) Then
                        xlSheet.Cells(iRow0 + j, 11) = m_ds.Tables(0).Rows.Item(i).Item("slkk11") - m_ds.Tables(0).Rows.Item(i).Item("slss11")
                    ElseIf (m_ds.Tables(0).Rows.Item(i).Item("slkk11") < m_ds.Tables(0).Rows.Item(i).Item("slss11")) Then
                        xlSheet.Cells(iRow0 + j, 12) = -(m_ds.Tables(0).Rows.Item(i).Item("slkk11") - m_ds.Tables(0).Rows.Item(i).Item("slss11"))
                    Else 'bang nhau ko in ra
                    End If
                    xlSheet.Cells(iRow0 + j, 13) = "*"
                    xlSheet.Cells(iRow0 + j, 14) = m_ds.Tables(0).Rows.Item(i).Item("sltl11")
                    gt_sosach += m_ds.Tables(0).Rows.Item(i).Item("slss11") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                    gt_kiemke += m_ds.Tables(0).Rows.Item(i).Item("slkk11") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                Else 'thang 7
                    xlSheet.Cells(iRow0 + j, 5) = m_ds.Tables(0).Rows.Item(i).Item("slkk17t")
                    'chu y khac
                    xlSheet.Cells(iRow0 + j, 6) = m_ds.Tables(0).Rows.Item(i).Item("sln17t") + m_ds.Tables(0).Rows.Item(i).Item("sln17")
                    xlSheet.Cells(iRow0 + j, 7) = m_ds.Tables(0).Rows.Item(i).Item("slx17t") + m_ds.Tables(0).Rows.Item(i).Item("slx17t")
                    '
                    xlSheet.Cells(iRow0 + j, 8) = m_ds.Tables(0).Rows.Item(i).Item("slss17")
                    xlSheet.Cells(iRow0 + j, 9) = m_ds.Tables(0).Rows.Item(i).Item("slkk17")
                    xlSheet.Cells(iRow0 + j, 10) = m_ds.Tables(0).Rows.Item(i).Item("slkk17") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                    If (m_ds.Tables(0).Rows.Item(i).Item("slkk17") > m_ds.Tables(0).Rows.Item(i).Item("slss17")) Then
                        xlSheet.Cells(iRow0 + j, 11) = m_ds.Tables(0).Rows.Item(i).Item("slkk17") - m_ds.Tables(0).Rows.Item(i).Item("slss17")
                    ElseIf (m_ds.Tables(0).Rows.Item(i).Item("slkk17") < m_ds.Tables(0).Rows.Item(i).Item("slss17")) Then
                        xlSheet.Cells(iRow0 + j, 12) = -(m_ds.Tables(0).Rows.Item(i).Item("slkk17") - m_ds.Tables(0).Rows.Item(i).Item("slss17"))
                    Else 'bang nhau ko in ra
                    End If
                    xlSheet.Cells(iRow0 + j, 13) = "*"
                    xlSheet.Cells(iRow0 + j, 14) = m_ds.Tables(0).Rows.Item(i).Item("sltl17")
                    gt_sosach += m_ds.Tables(0).Rows.Item(i).Item("slss17") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                    gt_kiemke += m_ds.Tables(0).Rows.Item(i).Item("slkk17") * m_ds.Tables(0).Rows.Item(i).Item("gle")
                End If
                i += 1
                j += 1
                stt_kho += 1
                If (i >= len) Then
                    Exit While
                End If
            End While
            '
        End While

        xlApp.ActiveWindow.Visible = False
        xlApp.Windows(1).Activate()
        '
        'duoi phieu
        xlSheet.Cells(iRow0 + j + 2, 2) = "Tổng cộng Kho có: " + CStr(stt_kho) + " mặt hàng"
        xlSheet.Cells(iRow0 + j + 3, 2) = "Giá trị theo sổ sách là:  " + CStr(gt_sosach) + "  đồng"
        xlSheet.Cells(iRow0 + j + 4, 2) = "Giá trị theo kiểm kê là:  " + CStr(gt_kiemke) + "  đồng" + "                              Chênh lệch kiểm kê và sổ sách là :  " + CStr(gt_kiemke - gt_sosach) + "  đồng"
        xlSheet.Cells(iRow0 + j + 6, 2) = "              In tại Phòng Vi tính Kho 706"
        xlSheet.Cells(iRow0 + j + 7, 4) = "                     TRƯỞNG BAN KẾ HOẠCH" + "                                                 " + "TRƯỞNG BAN TÀI CHÍNH" + "                                                 " + "CHỈ HUY KHO"
        mRange = xlSheet.Cells(iRow0 + j + 7, 4)
        mRange.Font.Bold = True
    End Sub

    Private Sub txtLoaiKho_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtLoaiKho.DoubleClick
        HienDMLoaiKho()
    End Sub

    Private Sub txtLoaiKho_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtLoaiKho.KeyDown
        If (e.KeyCode = Keys.Enter) Or (e.KeyCode = Keys.F1) Then
            HienDMLoaiKho()
        ElseIf (e.Modifiers = Keys.Control) And (e.KeyCode = Keys.Delete) Then
            txtLoaiKho.Text = ""
            m_loaikho = ""
        End If
    End Sub

    Public Sub HienDMLoaiKho()
        Dim strDMLoaiKho As String
        strDMLoaiKho = "select KH,TEN from tblDMKhac where L = 'L'"
        Dim ma, ten As String
        ma = ""
        ten = ""
        If (txtLoaiKho.Text <> "") Then
            Dim strSql As String
            strSql = "select TEN from tblDMKhac where L = 'L' and KH = '" & txtLoaiKho.Text & "'"
            Dim ten_loaikho As String
            ten_loaikho = BaseDB.ExecSql_DataValue(strSql)
            If (Not ten_loaikho Is Nothing) Then
                m_loaikho = txtLoaiKho.Text
                txtLoaiKho.Text = ten_loaikho
            Else
                'hien DM LoaiKho
                HienDM(strDMLoaiKho, "TEN", "KH", ten, ma)
                m_loaikho = ma
                txtLoaiKho.Text = ten
            End If
        Else
            'hien DM LoaiKho
            HienDM(strDMLoaiKho, "TEN", "KH", ten, ma)
            m_loaikho = ma
            txtLoaiKho.Text = ten
        End If
    End Sub

    Public Sub HienDM(ByVal strsql As String, ByVal strDM As String, ByVal strVM As String, _
                ByRef DM As String, ByRef VM As String)
        Dim frm As New frmChonDanhMucTheoListBox(strsql, strDM, strVM, "")
        frm.ShowDialog()
        DM = frm.DisplayMember
        VM = frm.ValueMember
    End Sub
   
End Class